package com.erp.framework.util.excelupload;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.erp.framework.util.logger.Logger;

public class ExcelUploadDao {

	protected String getInsertSql(){
		ArrayList columnList = ExcelRowFactory.getInstance().getProtoCellList();
		
		StringBuffer sql = new StringBuffer("INSERT INTO ");
		sql.append(ExcelUploadBuilder.TABLE_NAME+"( COMPANY_CODE, JOB_ID, LINE_NO ");
		for(int i=0; columnList!=null&&i<columnList.size(); i++){
			ExcelCellVo cellVo = (ExcelCellVo) columnList.get(i);
			sql.append(", "+cellVo.getDbColumn());
		}
		sql.append(") VALUES (?, ?, ? ");
		for(int j=0; columnList!=null&&j<columnList.size(); j++){
			sql.append(", ? ");
		}
		sql.append(")");
		Logger.log("insert Excel Value Sql:"+sql);
		return sql.toString();
	}

	protected void setValuesForInsert(PreparedStatement pStmt, ExcelRowVo vo)
	throws SQLException {
		int i = 1;
		
		ArrayList cellList = vo.getRowCellList();
		pStmt.setString(i++, vo.getCompanyCode());
		pStmt.setString(i++, vo.getJobId());
		pStmt.setInt(i++, vo.getExcelLineNo().intValue());
		for(int j=0; j<cellList.size(); j++){
			ExcelCellVo cellVo = (ExcelCellVo) cellList.get(j);
			setNullableString(pStmt, i++, cellVo.getCellValue());
		}
	}	
	
	protected void setNullableString(PreparedStatement pStmt, int index, String str) throws SQLException {
        if (str == null)
            pStmt.setNull(index, java.sql.Types.VARCHAR);
        else
            pStmt.setString(index, str);
    }
	
	public void insertDataToDb(ArrayList dataList, Connection conn) throws Exception {
		PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
			try {
				conn.setAutoCommit(false);
				String query = getInsertSql();
	            pStmt = conn.prepareStatement(query);
				for(int i = 0; i < dataList.size(); i++) {
					ExcelRowVo vo = (ExcelRowVo)dataList.get(i);
					Logger.log(vo.getJobId()+"  Excel Row Number: "+vo.getExcelLineNo());
		            setValuesForInsert(pStmt, vo);
		            int count = pStmt.executeUpdate();
		            if (count != 1)
		                throw new SQLException("Unable to insert ");
				}
				conn.commit();
			}catch (Exception e) {
				e.printStackTrace();
				Logger.logExceptions(this, e);
				conn.rollback();
				throw e;
			}
        }catch (Exception e) {
        	e.printStackTrace();
			Logger.logExceptions(this, e);
			throw e;
		} finally{
			try {
				if(rs!=null){
					rs.close();
				}
				if(pStmt!=null){
					pStmt.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
				Logger.logExceptions(this, e);
				throw e;
			}
		}
	}
}
